/**
 * 
 */
package sql.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 类描述： 
 * 创建人：别君羊 
 * 创建时间： 2014年7月3日 下午3:57:05 
 * 修改人：
 * 修改时间：
 * 修改备注：
 * @version: 1.0
 */
public class DataBaseUtil {
	/**
	 * 获取数据库表结构
	 * @param url
	 * @param user
	 * @param pwd
	 */
	public static List<Table> getDbTables(String url, String user, String pwd){
		Connection con=null;
		try{
//			Class.forName(pros.getProperty("db.driverClassName"));
			Properties props=new Properties();
			props.setProperty("user", user);
            props.setProperty("password", pwd);
            props.setProperty("remarks", "true"); //设置可以获取remarks信息 
            props.setProperty("useInformationSchema", "true");//设置可以获取tables remarks信息
            
			con = DriverManager.getConnection(url, props);
			List<Table> tables=new ArrayList<Table>();
			DatabaseMetaData dbMetaData = con.getMetaData();
			ResultSet tableSet = dbMetaData.getTables(null, "%", "%",
					new String[] { "TABLE" });
			while (tableSet.next()) {
				String tableName = tableSet.getString("TABLE_NAME");// 表名称
				String tableComment = tableSet.getString("REMARKS");// 表注释
				Table table=new Table(tableName, tableComment);
				table.setEntityName(getEntityNameByTableName(tableName));
				
				//获取主键
				ResultSet pkSet=dbMetaData.getPrimaryKeys(null, null, tableName);
				List<String> pkNames=new ArrayList<String>();
				while(pkSet.next()){
					pkNames.add(pkSet.getObject(4)+"");
				}
				pkSet.close();
				
				//获取列
				ResultSet columnSet = dbMetaData.getColumns(null, "%", tableName,
						"%");
				List<Column> columns=new ArrayList<Column>();
				while (columnSet.next()) {
					String columnName = columnSet.getString("COLUMN_NAME");// 列名称
					String columnComment = columnSet.getString("REMARKS");// 注释
					int columnType = columnSet.getInt("DATA_TYPE");// 列类型
					Column column=new Column(columnName, columnType, columnComment);
					column.setAttributeName(getAttributeNameByColumnName(columnName));
					column.setAttributeType(getAttributeTypeByColumnType(columnType));
					if(pkNames.contains(columnName)){
						column.setPrimaryKey(true);
						if(pkNames.size()==1){
							column.setAutoIncreatment(true);
							column.setNotNull(true);
							column.setUnique(true);
						}
					}
					columns.add(column);
				}
				table.setColumns(columns);
				tables.add(table);
				columnSet.close();
			}
			tableSet.close();
			return tables;
		}catch(Exception ex){
			ex.printStackTrace();
			try {
				if(!con.isClosed()){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}finally{
			try {
				if(!con.isClosed()){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	
	

	private static String getEntityNameByTableName(String tableName){
		StringBuffer name=new StringBuffer();
		if(tableName.indexOf("_")>-1){
			String[] strs=tableName.split("_");
			for(String str: strs){
				if(str.length()<=2){
					name.append(str.toUpperCase());
				}else{
					name.append(String.valueOf(str.charAt(0)).toUpperCase())
					.append(str.substring(1));
				}
			}
		}else{
			name.append(String.valueOf(tableName.charAt(0)).toUpperCase())
				.append(tableName.substring(1));
		}
		return name.toString()+"Bean";
	}
	
	private static String getAttributeNameByColumnName(String columnName){
		StringBuffer name=new StringBuffer();
		if(columnName.indexOf("_")>-1){
			String[] strs=columnName.split("_");
			for(int i=0; i<strs.length; i++){
				String str=strs[i];
				if(i==0){
					name.append(str);
				}else{
					name.append(String.valueOf(str.charAt(0)).toUpperCase())
					.append(str.substring(1));
				}
			}
		}else{
			name.append(columnName);
		}
		return name.toString();
	}
	
	
	private static String getAttributeTypeByColumnType(int columnType){
		switch (columnType) {
		case Types.INTEGER:	case Types.BIT: case Types.TINYINT: case Types.SMALLINT:
			return "Integer";
		case Types.BIGINT: 
			return "Long";
		case Types.BOOLEAN: 
			return "boolean";
		case Types.FLOAT:
			return "Float";
		case Types.DOUBLE:	case Types.NUMERIC:	case Types.DECIMAL: 
			return "Double";
		case Types.TIMESTAMP: case Types.TIME:
			return "Timestamp";
		case Types.DATE:
			return "Date";
		case Types.VARCHAR: case Types.CHAR: case Types.LONGNVARCHAR: 
		case Types.LONGVARCHAR:case Types.NCHAR:case Types.NVARCHAR:
			return "String";
		default:
			return "String";
		}
	}
}
